﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using famails.common;
using famails.dto;
using famails.App_Code.source.dto;
using System.Collections.Generic;

namespace famails.dao
{
    /// <summary>
    /// Summary description for ArticleDTO
    /// </summary>
    public class CM_OrderDAO
    {
        public CM_OrderDAO()
        {

        }

        public DataTable GetByID(decimal ID, int UserID)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_Order_Get4Edit", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@OrderID", SqlDbType.Decimal).Value = ID;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable Search(string ordercode, string deliverycode, string email, string phone, string name, string from, string to, int groupid, int userid)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_Order_Search", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            cmd.Parameters.Add("@from", SqlDbType.NVarChar).Value = from;
            cmd.Parameters.Add("@to", SqlDbType.NVarChar).Value = to;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
            cmd.Parameters.Add("@groupid", SqlDbType.Int).Value = groupid;
            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;
            cmd.Parameters.Add("@deliverycode", SqlDbType.NVarChar).Value = deliverycode;
            cmd.Parameters.Add("@ordercode", SqlDbType.NVarChar).Value = ordercode;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public object Insert(CM_OrderDTO dto)
        {
            object obj = 0; SqlTransaction tran = null;
            try
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [tblCM_Orders]
                                       ([Amount]
                                       ,[OrderDate]
                                       ,[DeliveryDate]
                                       ,[Status]
                                       ,[Payment]
                                       ,[CustomerID]
                                       ,[Delivery], DeliveryCode, OrderCode, DeliveryFee, Note
                                       , DiscountAmount, DiscountCode, DiscountValue, Fix0_Per1, MinAmount, RecieverName, RecieverAddress, RecieverPhone, Reciever
                                       ,[CreateBy], Deposit)
                                 VALUES
                                       (@Amount
                                       ,@OrderDate
                                       ,@DeliveryDate
                                       ,@Status
                                       ,@Payment
                                       ,@CustomerID
                                       ,@Delivery, @DeliveryCode, @OrderCode, @DeliveryFee, @Note
                                       ,@DiscountAmount, @DiscountCode, @DiscountValue, @Fix0_Per1, @MinAmount, @RecieverName, @RecieverAddress, @RecieverPhone, @Reciever
                                       ,@CreateBy, @Deposit) select @@identity", ConnectionData._MyConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = dto.Amount;
                cmd.Parameters.Add("@Deposit", SqlDbType.Decimal).Value = dto.Deposit;
                cmd.Parameters.Add("@DeliveryFee", SqlDbType.Decimal).Value = dto.DeliveryFee;
                cmd.Parameters.Add("@OrderDate", SqlDbType.Date).Value = dto.OrderDate;
                cmd.Parameters.Add("@DeliveryDate", SqlDbType.Date).Value = dto.DeliveryDate;
                cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = dto.Status;
                cmd.Parameters.Add("@Payment", SqlDbType.NVarChar).Value = dto.Payment;
                cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = dto.CustomerID;
                cmd.Parameters.Add("@Delivery", SqlDbType.NVarChar).Value = dto.Delivery;
                cmd.Parameters.Add("@CreateBy", SqlDbType.Int).Value = dto.createBy;
                cmd.Parameters.Add("@DeliveryCode", SqlDbType.NVarChar).Value = dto.DeliveryCode;
                cmd.Parameters.Add("@OrderCode", SqlDbType.NVarChar).Value = dto.OrderCode;
                cmd.Parameters.Add("@Note", SqlDbType.NVarChar).Value = dto.Note;

                cmd.Parameters.Add("@DiscountCode", SqlDbType.NVarChar).Value = dto.DiscountCode;
                cmd.Parameters.Add("@DiscountAmount", SqlDbType.Decimal).Value = dto.DiscountAmount;
                cmd.Parameters.Add("@DiscountValue", SqlDbType.Decimal).Value = dto.DiscountValue;
                cmd.Parameters.Add("@Fix0_Per1", SqlDbType.Int).Value = dto.Fix0_Per1;
                cmd.Parameters.Add("@MinAmount", SqlDbType.Decimal).Value = dto.MinAmount;
                cmd.Parameters.Add("@Reciever", SqlDbType.Bit).Value = dto.Reciever;
                cmd.Parameters.Add("@RecieverName", SqlDbType.NVarChar).Value = dto.RecieverName;
                cmd.Parameters.Add("@RecieverAddress", SqlDbType.NVarChar).Value = dto.RecieverAddress;
                cmd.Parameters.Add("@RecieverPhone", SqlDbType.NVarChar).Value = dto.RecieverPhone;
                tran = cmd.Connection.BeginTransaction();
                cmd.Transaction = tran;
                obj = cmd.ExecuteScalar();
                dto.OrderID = (decimal)obj;
                AddOrderDetail(dto, ref tran);
                tran.Commit();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                if (tran != null) tran.Rollback();
                common.Common.logs("Add Order", ex, Common.logType.error);
                obj = 0;
            }
            return obj;
        }
        public void AddOrderDetail(CM_OrderDTO order, ref SqlTransaction tran)
        {
            foreach (CM_OrderDetailDTO dto in order.Items)
            {
                string sql = "SP_CM_Product_AddProduct";
                SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductCode", SqlDbType.NVarChar).Value = dto.ProductCode;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = dto.ProductName;
                cmd.Parameters.Add("@CreateBy", SqlDbType.Int).Value = order.createBy;
                cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = dto.Price;
                cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = dto.GroupID;
                object id = cmd.ExecuteScalar();
                decimal pid = 0; decimal.TryParse(id + "", out pid);
                dto.ProductID = pid > 0 ? pid : dto.ProductID;
                sql = "insert into tblCM_OrderDetails(OrderID, ProductID, Qty, Price, VAT, VATAmount,Amount)values(@OrderID, @ProductID, @Qty, @Price, @VAT, @VATAmount,@Amount)";
                cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@OrderID", SqlDbType.Decimal).Value = order.OrderID;
                cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = dto.ProductID;
                cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = dto.Qty;
                cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = dto.Price;
                cmd.Parameters.Add("@VAT", SqlDbType.Decimal).Value = dto.VAT;
                cmd.Parameters.Add("@VATAmount", SqlDbType.Decimal).Value = dto.VATAmount;
                cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = dto.Amount;
                cmd.ExecuteNonQuery();
            }
        }

        public DataTable GetDetail(decimal OrderID)
        {
            SqlCommand cmd = new SqlCommand("Select d.*, p.ProductCode, p.ProductName From tblCM_OrderDetails d inner join tblCM_Products p on p.Productid = d.productid where orderid=" + OrderID, ConnectionData._MyConnection);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public bool Edit(CM_OrderDTO dto)
        {
            int i = 0; SqlTransaction tran = null;
            try
            {
                SqlCommand cmd = new SqlCommand(@"update tblCM_Orders set [Amount]=@Amount
                                       ,[OrderDate]=@OrderDate
                                       ,[DeliveryDate]=@DeliveryDate
                                       ,[Status]=@Status
                                       ,[Payment]=@Payment
                                       ,[CustomerID]=@CustomerID, DeliveryCode = @DeliveryCode, OrderCode = @OrderCode, DeliveryFee=@DeliveryFee, Note=@Note
                                       , DiscountCode=@DiscountCode, DiscountValue=@DiscountValue, DiscountAmount=@DiscountAmount, Fix0_Per1=@Fix0_Per1, MinAmount=@MinAmount, RecieverName=@RecieverName, RecieverAddress=@RecieverAddress, RecieverPhone=@RecieverPhone, Reciever=@Reciever
                                       ,[Delivery]=@Delivery, Deposit=@Deposit where OrderID = @id", ConnectionData._MyConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = dto.Amount;
                cmd.Parameters.Add("@Deposit", SqlDbType.Decimal).Value = dto.Deposit;
                cmd.Parameters.Add("@DeliveryFee", SqlDbType.Decimal).Value = dto.DeliveryFee;
                cmd.Parameters.Add("@OrderDate", SqlDbType.Date).Value = dto.OrderDate;
                cmd.Parameters.Add("@DeliveryDate", SqlDbType.Date).Value = dto.DeliveryDate;
                cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = dto.Status;
                cmd.Parameters.Add("@Payment", SqlDbType.NVarChar).Value = dto.Payment;
                cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = dto.CustomerID;
                cmd.Parameters.Add("@Delivery", SqlDbType.NVarChar).Value = dto.Delivery;
                cmd.Parameters.Add("@DeliveryCode", SqlDbType.NVarChar).Value = dto.DeliveryCode;
                cmd.Parameters.Add("@OrderCode", SqlDbType.NVarChar).Value = dto.OrderCode;
                cmd.Parameters.Add("@Note", SqlDbType.NVarChar).Value = dto.Note;
                cmd.Parameters.Add("@DiscountValue", SqlDbType.Decimal).Value = dto.DiscountValue;
                cmd.Parameters.Add("@DiscountAmount", SqlDbType.Decimal).Value = dto.DiscountAmount;
                cmd.Parameters.Add("@Fix0_Per1", SqlDbType.Int).Value = dto.Fix0_Per1;
                cmd.Parameters.Add("@MinAmount", SqlDbType.Decimal).Value = dto.MinAmount;
                cmd.Parameters.Add("@DiscountCode", SqlDbType.NVarChar).Value = dto.DiscountCode;
                cmd.Parameters.Add("@Reciever", SqlDbType.Bit).Value = dto.Reciever;
                cmd.Parameters.Add("@RecieverName", SqlDbType.NVarChar).Value = dto.RecieverName;
                cmd.Parameters.Add("@RecieverAddress", SqlDbType.NVarChar).Value = dto.RecieverAddress;
                cmd.Parameters.Add("@RecieverPhone", SqlDbType.NVarChar).Value = dto.RecieverPhone;
                cmd.Parameters.Add("@Id", SqlDbType.Decimal).Value = dto.OrderID;
                tran = cmd.Connection.BeginTransaction();
                cmd.Transaction = tran;
                i = cmd.ExecuteNonQuery();
                string sql = "delete from tblCM_OrderDetails where orderid =" + dto.OrderID;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                AddOrderDetail(dto, ref tran);
                tran.Commit();
                cmd.Dispose();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                common.Common.logs("Add Order", ex, Common.logType.error);
                if (tran != null) tran.Rollback();
                i = 0;
                throw ex;
            }
            return i > 0;
        }

        public bool Delete(int id)
        {
            SqlCommand cmd = new SqlCommand("update tblCM_Orders set isdelete=1 where OrderID = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }



        internal bool CheckExistsOrderCode(string code, decimal orderid)
        {
            SqlCommand cmd = new SqlCommand("Select ordercode from tblCM_Orders where IsDelete <> 1 and ordercode='" + code + "' and OrderID <>" + orderid, ConnectionData._MyConnection);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table.Rows.Count > 0;
        }

        internal DataTable GetOrdersByDiscountID(object ID)
        {
            SqlCommand cmd = new SqlCommand("SP_Order_GetOrdersByDiscountID " + ID, ConnectionData._MyConnection);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
    }
}
